package com.hqyj.emb.system.dao.Impl;

import com.hqyj.emb.system.dao.UserDao;
import com.hqyj.emb.system.model.User;
import com.hqyj.emb.system.model.User2;
import frame.dbuitl.JdbcUtils;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class UserDaoImpl implements UserDao {

    @Override
    public User queryAllUser(User user) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        // 查询数据库
        // prepareStatement与Statement区别
        try {
            conn = JdbcUtils.getConnection();
            String sql = "SELECT * FROM sys_user WHERE userName=? and password=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, user.getUserName());
            pstmt.setString(2, user.getPassword());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                User user_db = new User();
                user_db.setUserId(rs.getInt("userId"));
                user_db.setName(rs.getString("name"));
                user_db.setSex(rs.getString("sex"));
                user_db.setAdress(rs.getString("adress"));
                user_db.setTel(rs.getString("tel"));
                user_db.setUserName(rs.getString("userName"));
                user_db.setPassword(rs.getString("password"));
                return user_db;
            }
        } catch (ClassNotFoundException | IOException | SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<User> queryUserByDb() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        // 查询数据库
        // prepareStatement与Statement区别
        List<User> list=new ArrayList<>();
        try {
            conn = JdbcUtils.getConnection();
            String sql = "SELECT * FROM sys_user ";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                User user_db = new User();
                user_db.setUserId(rs.getInt("userId"));
                user_db.setName(rs.getString("name"));
                user_db.setSex(rs.getString("sex"));
                user_db.setAdress(rs.getString("adress"));
                user_db.setTel(rs.getString("tel"));
                user_db.setUserName(rs.getString("userName"));
                user_db.setPassword(rs.getString("password"));
                list.add(user_db);
            }

        } catch (ClassNotFoundException | IOException | SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int addUser(User user) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "INSERT INTO  sys_user (userId,name,sex,adress,tel,userName,password) VALUES(?,?,?,?,?,?,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, user.getUserId());
            pstmt.setString(2, user.getName());
            pstmt.setString(3, user.getSex());
            pstmt.setString(4, user.getAdress());
            pstmt.setString(5, user.getTel());
            pstmt.setString(6, user.getUserName());
            pstmt.setString(7, user.getPassword());

            int m = pstmt.executeUpdate();
            return m;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    @Override
    public int deleteUserByUser(int userIdST) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        // 查询数据库
        // prepareStatement与Statement区别
        try {
            conn = JdbcUtils.getConnection();
            String sql = "DELETE FROM sys_user WHERE userId=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userIdST);

            int m = pstmt.executeUpdate();
            return  m;
        } catch (ClassNotFoundException | IOException | SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public User queryUserByUserId(int userId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        // 查询数据库
        // prepareStatement与Statement区别
        try {
            conn = JdbcUtils.getConnection();
            String sql = "SELECT * FROM sys_user WHERE userId=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                User user_db = new User();
                user_db.setUserId(rs.getInt("userId"));
                user_db.setName(rs.getString("name"));
                user_db.setSex(rs.getString("sex"));
                user_db.setAdress(rs.getString("adress"));
                user_db.setTel(rs.getString("tel"));
                user_db.setUserName(rs.getString("userName"));
                user_db.setPassword(rs.getString("password"));
                return user_db;
            }
        } catch (ClassNotFoundException | IOException | SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    @Override
    public int updateUser(User user) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = JdbcUtils.getConnection();
            String sql = "UPDATE sys_user SET name=?,sex=?,adress=?,tel=?,userName=?,password=? WHERE userId=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, user.getName());
            pstmt.setString(2, user.getSex());
            pstmt.setString(3, user.getAdress());
            pstmt.setString(4, user.getTel());
            pstmt.setString(5, user.getUserName());
            pstmt.setString(6, user.getPassword());
            pstmt.setInt(7, user.getUserId());

            int m = pstmt.executeUpdate();
            return m;

        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int queryUser2TotalRecord() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            String sql = "SELECT count(*) FROM  sys_user";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                int count = rs.getInt(1);
                return count;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<User> queryCurrentPageDataList(int i, int ps) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "SELECT * FROM  sys_user LIMIT ?,?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, i);
            pstmt.setInt(2, ps);
            rs = pstmt.executeQuery();
            List<User> user2List = new ArrayList<>();
            while (rs.next()) {
                User user2_db = new User();
                user2_db.setUserId(rs.getInt("userId"));
                user2_db.setName(rs.getString("name"));
                user2_db.setSex(rs.getString("sex"));
                user2_db.setAdress(rs.getString("adress"));
                user2_db.setTel(rs.getString("tel"));
                user2_db.setUserName(rs.getString("userName"));
                user2_db.setPassword(rs.getString("password"));
                user2List.add(user2_db);
            }
            return user2List;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}
